package memberPractice;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class MemberPracticeDAO implements memberPracticeService {
	
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521:xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String insertSQL =
			"insert into Memberpractice(userid, jumin, pwd, zip, addr, tel, mp, pay)"
			+ " values (id.nextvalue, ?, ?, ?, ?, ?, ?, ?)";
	private static final String updateSQL = "";
	private static final String deleteSQL = "";
	private static final String selectSQL = "select * from memberpractice order by userid asc";
	private static final String selectOneSQL = "";
	
	@Override
	public int insertMemberPractice(MemberPracticeVO mp) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, mp.getJumin());
				ps.setString(2, mp.getPwd());
				ps.setString(3, mp.getZip());
				ps.setString(4, mp.getAddr());
				ps.setString(5, mp.getTel());
				ps.setString(6, mp.getMp());
				ps.setString(7, mp.getPay());
			
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return cnt;
	}
	@Override
	public int updateMemberPractice(MemberPracticeVO mp) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		
		return cnt;
	}
	@Override
	public int deleteMemberPractice(String m) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
			
			cnt = ps.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return cnt;
	}
	@Override
	public ArrayList<MemberPracticeVO> selectMemberPractice() {
		ArrayList<MemberPracticeVO> data = new ArrayList<MemberPracticeVO>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while (rs.next()) {
				MemberPracticeVO mp = 
						new MemberPracticeVO(rs.getString(1), rs.getString(2),
											 "", "", "", "", rs.getString(3), "");
				data.add(mp);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) {
				try { rs.close(); } catch (Exception e) { }
			}
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		
		
		return data;
	}
	@Override
	public String selectOneMemberPractice(String m) {
		return null;
	}
	
	

}









